<?php
/*
统计
*/
class StaticController extends MyClass_Action {
	protected $controller;
	protected $action;
	protected $_user;
	protected $groupdbAdapter;
	
	public function init() {
		$this->commonAction();	
	}
	
	public function indexAction(){
		try {
			$start_date = $this->_request->getParam ( 'start_date' ); 
			$end_date =  $this->_request->getParam ( 'end_date' );
			$groupid = ( int ) $this->_request->getParam ( 'groups' );
			$this->view->start_date=$start_date;
			$this->view->end_date=$end_date;
			$this->view->groups=$groupid;
		if($groupid){
			//显示校区列表
			$sql="SELECT schoolid,schoolname FROM school where groupid=".$groupid." order by schoolid asc";
			$school=$this->_dbAdapter->fetchAssoc ( $sql );
			$this->view->school= $school; 
			//print_r($school);
			
		    //获取每个校区学生数
			$sql="SELECT schoolid,count(distinct uid) as num  FROM group_student_class_parents_v where inactive=0 and groupid=".$groupid." group by schoolid";
		    $student=$this->_dbAdapter->fetchPairs($sql);
		    //print_r($student);exit;
			
			//获取天传入的变量
			$now_date=date('Y-m-d');
			if(strtotime($start_date)<=0 || strtotime($start_date) > strtotime($now_date))
				$start_date=$now_date;
			if(!strtotime($end_date) || $end_date>$now_date)
				$end_date=$now_date;
			if($start_date>$end_date)
				$end_date=$start_date;

			//获取期间每个校区发布的图片数
		    $sql='SELECT schoolid,COUNT(articlefilesid) as num FROM `article_files_class_v` where groupid='.$groupid.' and fext<>"mp4" and isdelete=0 and ischecked=1 and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'" GROUP BY schoolid';
			//echo $sql;exit;
			$picture=$this->_dbAdapter->fetchPairs($sql);
			
			//获取期间每个校区发布的视频数
		    $sql='SELECT schoolid,COUNT(articlefilesid) as num FROM `article_files_class_v` where groupid='.$groupid.' and  fext="mp4" and isdelete=0 and ischecked=1 and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'" GROUP BY schoolid';
			$video=$this->_dbAdapter->fetchPairs($sql);
			
			
			//获取每个校区学生接收的图片、视频数
			foreach($school as $key=>$value){
				//学生数
				if($student[$key]){
					$school[$key]['student']=$student[$key];
				}else{
					$school[$key]['student']=0;
				}
				
				//获取每个校区学生uid
				$sql="select uid from student where schoolid=".$key;
				$uid=$this->_dbAdapter->fetchCol($sql);
				$uid_student=implode("','",$uid);
				//print_r(count($uid));echo '<br />';
				
/*				//获取期间付费学生家长数
				$sql="SELECT COUNT(DISTINCT uid_student) from pay_order WHERE uid_student in ('".$uid_student."') and isdelete=0 and isover=0 and from_unixtime(addtime,'%Y-%m-%d')>='".$start_date."' and from_unixtime(addtime,'%Y-%m-%d')<='".$end_date."' ";
				$pay=$this->_dbAdapter->fetchOne($sql);
				//echo $sql;exit;
				//获取所有付费学生家长数
				$sql="SELECT COUNT(DISTINCT uid_student) from pay_order WHERE uid_student in ('".$uid_student."') and isdelete=0 and isover=0";
				$payall=$this->_dbAdapter->fetchOne($sql);
				//print_r($payall);echo '<br />';
				//付费数
				if($pay){
					$school[$key]['pay']=$pay;
				}else{
					$school[$key]['pay']=0;
				}
				if($payall){
					$school[$key]['payed']=$payall;
					$school[$key]['unpay']=$student[$key]-$payall;
					$school[$key]['payrate']=round($payall/$student[$key]*100,2);
					$school[$key]['payrate'].='%';
				}else{
					$school[$key]['payed']=0;
					$school[$key]['unpay']=$student[$key];
					$school[$key]['payrate']=0;
				}
*/				
				//期间老师拍照数
				if($picture[$key]){
					$school[$key]['picture']=$picture[$key];
					//$school[$key]['sendpicrate']=round($picture[$key]/$student[$key],0);
				}else{
					$school[$key]['picture']=0;
					//$school[$key]['sendpicrate']=0;
				}
/*				//期间学生接收照片数
				$sql='SELECT articlefilesid,memberlist FROM `article_files_class_v` where groupid='.$groupid.' and fext<>"mp4" and isdelete=0 and ischecked=1 and schoolid='.$key.' and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'"';
				$recieve=$this->_dbAdapter->fetchPairs($sql);
				//print_r($recieve);
				$rec=0;
				if($recieve){
					foreach($recieve as $key1=>$value1){
						$rec+=count(explode(',',$value1));
					}
				}
				$school[$key]['recpicnum']=$rec;
				$school[$key]['recpicrate']=round($rec/$student[$key],0);
				
				//所有时间学生接收照片数
				$sql='SELECT articlefilesid,memberlist FROM `article_files_class_v` where groupid='.$groupid.' and fext<>"mp4" and isdelete=0 and ischecked=1 and schoolid='.$key;
				$recieve1=$this->_dbAdapter->fetchPairs($sql);
				$rec=0;
				if($recieve1){
					foreach($recieve1 as $key2=>$value2){
						$rec+=count(explode(',',$value2));
					}
				}
				$school[$key]['recpicallnum']=$rec;
				$school[$key]['recpicallrate']=round($rec/$student[$key],0);
*/				
				//期间老师拍视频数
				if($video[$key]){
					$school[$key]['video']=$video[$key];
					//$school[$key]['sendvideorate']=round($video[$key]/$student[$key],0);
				}else{
					$school[$key]['video']=0;
					//$school[$key]['sendvideorate']=0;
				}
/*				//期间学生接收视频数
				$sql='SELECT articlefilesid,memberlist FROM `article_files_class_v` where groupid='.$groupid.' and fext="mp4" and isdelete=0 and ischecked=1 and schoolid='.$key.' and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'"';
				$recievevideo=$this->_dbAdapter->fetchPairs($sql);
				$rec=0;
				if($recievevideo){
					foreach($recievevideo as $key3=>$value3){
						$rec+=count(explode(',',$value3));
					}
				}
				$school[$key]['recvideonum']=$rec;
				$school[$key]['recvideorate']=round($rec/$student[$key],0);
				
				//所有时间学生接收视频数
				$sql='SELECT articlefilesid,memberlist FROM `article_files_class_v` where groupid='.$groupid.' and fext="mp4" and isdelete=0 and ischecked=1 and schoolid='.$key;
				$recieve2=$this->_dbAdapter->fetchPairs($sql);
				$rec=0;
				if($recieve2){
					foreach($recieve2 as $key4=>$value4){
						$rec+=count(explode(',',$value4));
					}
				}
				$school[$key]['recvideoallnum']=$rec;
				$school[$key]['recvideoallrate']=round($rec/$student[$key],0);
*/				
				//
			}//exit;
			
			//print_r($school);
			//exit;

			$this->view->school=$school;
			
		}
			//print_r($start_date);print_r($end_date);print_r($groupid);exit;
		} catch ( Exception $e ) {
			$this->feedback ( $e->getMessage (), 'note', 'javascript:window.history.back();', 'warning' );
		}
	}

	 
//查看详细统计
	public function statisticsAction(){
		try {
			$this->view->groupid=$groupid = ( int ) $this->_request->getParam ( 'groupid' );
			$this->view->schoolid = $schoolid = ( int ) $this->_request->getParam ( 'schoolid' );
			$this->view->classid = $classid = ( int ) $this->_request->getParam ( 'classid' );
			$start_date = $this->_request->getParam ( 'start_date' ); 
			$end_date =  $this->_request->getParam ( 'end_date' );
			//print_r($groupid);print_r($schoolid);print_r($classid);print_r($start_date);print_r($end_date);
			if(!$groupid || !$schoolid || !$classid){
				throw new Exception ( '参数错误' );
			}
			
			//取学校名
			$sql='select schoolname from school where schoolid='.$schoolid;
			$this->view->schoolname=$this->_dbAdapter->fetchOne ( $sql );
			//print_r($schoolname);
			//显示班级列表
			$sql="SELECT uid,classname FROM class where schoolid=".$schoolid." order by uid asc ";
			$class=$this->_dbAdapter->fetchAssoc ( $sql );
			$this->view->class=$class;
			//print_r($class);
			//获取数据
			if($classid){
				//取班级名
				$this->view->classname=$class[$classid]['classname'];
			   
			   //本班级学员列表
			   $sql="select studentid,cnname from group_student_class_parents_v where inactive=0 and classid=".$classid;
			   $student=$this->_dbAdapter->fetchAssoc($sql);
		       
			   if(!$student)
					 throw new Exception ( '本班学生列表为空' );  
					  
			    foreach($student as $key => $value){
					$student[$key]['articleNum']=0;
					$student[$key]['imgNum']=0;
			    } 	
			    //print_r($student);exit;
				//获取天传入的变量
			    
				$now_date=date('Y-m-d');
				if(strtotime($start_date)<=0 || strtotime($start_date) > strtotime($now_date))
					$start_date=$now_date;
					
				if(!strtotime($end_date) || $end_date>$now_date)
					$end_date=$now_date;
				
				if($start_date>$end_date)
					$end_date=$start_date;

				$this->view->start_date=$start_date;
				$this->view->end_date=$end_date;

				//获取时间段的所有文章
				$sql='SELECT articlekey FROM `article` where groupid='.$groupid.' and isdelete=0 and classid='.$classid.' and  from_unixtime(addtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(addtime,"%Y-%m-%d")<="'.$end_date.'" ';
				$articlekeylist=$this->_dbAdapter->fetchCol($sql);

				$articlekeylist=implode('","',$articlekeylist);
				
				$sql='SELECT studentid,articlekey from article_student WHERE articlekey in ("'.$articlekeylist.'")';
				$studentArticlekey=$this->_dbAdapter->fetchAll($sql);
				
				if($articlekeylist){
					//获取这些文章包含的图片数
					$sql='SELECT articlekey,COUNT(cloudfilename) as num from article_files where isdelete=0 and articlekey in ("'.$articlekeylist.'") GROUP BY articlekey';
					$imgcount=$this->_dbAdapter->fetchPairs($sql);
					
					//按学员列表获取所有学员统计

					foreach($student as $key => $value){
					   foreach($studentArticlekey as $key1=> $value1){
						   //print_r($imgcount[$value1['articlekey']]);
						   if($value1['studentid']==$value['studentid']){
							 $student[$key]['articleNum']++;
							 if(isset($imgcount[$value1['articlekey']]))
								$student[$key]['imgNum'] +=$imgcount[$value1['articlekey']];
						   }
					  }
				   }
				   
				}
				$this->view->student=$student;
				
				$this->view->studentcount=count($student);
			}
			
		} catch ( Exception $e ) {
			$this->feedback ( $e->getMessage (), 'note', 'javascript:window.history.back();', 'warning' );
		}
	}

	 
/*//查看学校统计
	public function statisticsallAction(){
		try {
			$this->view->groupid=$groupid = ( int ) $this->_request->getParam ( 'groupid' );
			if(!$groupid){
				//跳转到机构列表
				throw new Exception ( '参数错误' );
			}

			//显示校区列表
			$sql="SELECT schoolid,schoolname FROM school where 1 order by schoolid asc";
			$school=$this->_dbAdapter->fetchAssoc ( $sql );
			$this->view->school= $school; 
			//print_r($school);
			
		    //获取每个校区学生数
		    $sql="select schoolid,count(uid) as num from student where groupid=".$groupid." group by schoolid";
		    $student=$this->_dbAdapter->fetchPairs($sql);
		    //print_r($student);
			
			//获取天传入的变量
			$start_date = $this->_request->getParam ( 'start_date' ); 
			$end_date =  $this->_request->getParam ( 'end_date' );
			$now_date=date('Y-m-d');
			if(strtotime($start_date)<=0 || strtotime($start_date) > strtotime($now_date))
				$start_date=$now_date;
			if(!strtotime($end_date) || $end_date>$now_date)
				$end_date=$now_date;
			if($start_date>$end_date)
				$end_date=$start_date;
			$this->view->start_date=$start_date;
			$this->view->end_date=$end_date;

			//获取期间每个校区发布的图片数
		    $sql='SELECT schoolid,COUNT(articlefilesid) as num FROM `article_files_class_v` where groupid='.$groupid.' and fext<>"mp4" and isdelete=0 and ischecked=1 and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'" GROUP BY schoolid';
			$picture=$this->_dbAdapter->fetchPairs($sql);
			
			//获取期间每个校区发布的视频数
		    $sql='SELECT schoolid,COUNT(articlefilesid) as num FROM `article_files_class_v` where groupid='.$groupid.' and  fext="mp4" and isdelete=0 and ischecked=1 and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'" GROUP BY schoolid';
			$video=$this->_dbAdapter->fetchPairs($sql);
			
			
			//获取每个校区学生接收的图片、视频数
			foreach($school as $key=>$value){
				//学生数
				if($student[$key]){
					$school[$key]['student']=$student[$key];
				}else{
					$school[$key]['student']=0;
				}
				
				//获取每个校区学生uid
				$sql="select uid from student where schoolid=".$key;
				$uid=$this->_dbAdapter->fetchCol($sql);
				$uid_student=implode("','",$uid);
				//print_r(count($uid));echo '<br />';
				
				//获取期间付费学生家长数
				$sql="SELECT COUNT(DISTINCT uid_student) from pay_order WHERE uid_student in ('".$uid_student."') and isdelete=0 and isover=0 and from_unixtime(addtime,'%Y-%m-%d')>='".$start_date."' and from_unixtime(addtime,'%Y-%m-%d')<='".$end_date."' ";
				$pay=$this->_dbAdapter->fetchOne($sql);
				//echo $sql;exit;
				//获取所有付费学生家长数
				$sql="SELECT COUNT(DISTINCT uid_student) from pay_order WHERE uid_student in ('".$uid_student."') and isdelete=0 and isover=0";
				$payall=$this->_dbAdapter->fetchOne($sql);
				//print_r($payall);echo '<br />';
				//付费数
				if($pay){
					$school[$key]['pay']=$pay;
				}else{
					$school[$key]['pay']=0;
				}
				if($payall){
					$school[$key]['payed']=$payall;
					$school[$key]['unpay']=$student[$key]-$payall;
					$school[$key]['payrate']=round($payall/$student[$key]*100,2);
					$school[$key]['payrate'].='%';
				}else{
					$school[$key]['payed']=0;
					$school[$key]['unpay']=$student[$key];
					$school[$key]['payrate']=0;
				}
				
				//期间老师拍照数
				if($picture[$key]){
					$school[$key]['picture']=$picture[$key];
					$school[$key]['sendpicrate']=round($picture[$key]/$student[$key],0);
				}else{
					$school[$key]['picture']=0;
					$school[$key]['sendpicrate']=0;
				}
				//期间学生接收照片数
				$sql='SELECT articlefilesid,memberlist FROM `article_files_class_v` where groupid='.$groupid.' and fext<>"mp4" and isdelete=0 and ischecked=1 and schoolid='.$key.' and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'"';
				$recieve=$this->_dbAdapter->fetchPairs($sql);
				//print_r($recieve);
				$rec=0;
				if($recieve){
					foreach($recieve as $key1=>$value1){
						$rec+=count(explode(',',$value1));
					}
				}
				$school[$key]['recpicnum']=$rec;
				$school[$key]['recpicrate']=round($rec/$student[$key],0);
				
				//所有时间学生接收照片数
				$sql='SELECT articlefilesid,memberlist FROM `article_files_class_v` where groupid='.$groupid.' and fext<>"mp4" and isdelete=0 and ischecked=1 and schoolid='.$key;
				$recieve1=$this->_dbAdapter->fetchPairs($sql);
				$rec=0;
				if($recieve1){
					foreach($recieve1 as $key2=>$value2){
						$rec+=count(explode(',',$value2));
					}
				}
				$school[$key]['recpicallnum']=$rec;
				$school[$key]['recpicallrate']=round($rec/$student[$key],0);
				
				//期间老师拍视频数
				if($video[$key]){
					$school[$key]['video']=$video[$key];
					$school[$key]['sendvideorate']=round($video[$key]/$student[$key],0);
				}else{
					$school[$key]['video']=0;
					$school[$key]['sendvideorate']=0;
				}
				//期间学生接收视频数
				$sql='SELECT articlefilesid,memberlist FROM `article_files_class_v` where groupid='.$groupid.' and fext="mp4" and isdelete=0 and ischecked=1 and schoolid='.$key.' and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'"';
				$recievevideo=$this->_dbAdapter->fetchPairs($sql);
				$rec=0;
				if($recievevideo){
					foreach($recievevideo as $key3=>$value3){
						$rec+=count(explode(',',$value3));
					}
				}
				$school[$key]['recvideonum']=$rec;
				$school[$key]['recvideorate']=round($rec/$student[$key],0);
				
				//所有时间学生接收视频数
				$sql='SELECT articlefilesid,memberlist FROM `article_files_class_v` where groupid='.$groupid.' and fext="mp4" and isdelete=0 and ischecked=1 and schoolid='.$key;
				$recieve2=$this->_dbAdapter->fetchPairs($sql);
				$rec=0;
				if($recieve2){
					foreach($recieve2 as $key4=>$value4){
						$rec+=count(explode(',',$value4));
					}
				}
				$school[$key]['recvideoallnum']=$rec;
				$school[$key]['recvideoallrate']=round($rec/$student[$key],0);
				
				//
			}//exit;
			
			//print_r($school);
			

			$this->view->school=$school;
			
		} catch ( Exception $e ) {
			$this->feedback ( $e->getMessage (), 'note', 'javascript:window.history.back();', 'warning' );
		}
	}
*/	
//查看统计
	public function onlinestatisticsAction(){
		try {
			$this->view->groups=$groupid = ( int ) $this->_request->getParam ( 'groups' );
			$where='';
			if($groupid){
				$where.=' and groupid='.$groupid;
			}


			//取集团校区  统计每个学校学生数
			$sql="SELECT schoolid,schoolname,count(distinct uid) as student,groupid,groupname FROM group_student_class_parents_v where ismainid=1 and inactive=0  ".$where." group by schoolid ORDER BY groupid asc, schoolid asc";
			//echo $sql;echo '<br />';
			$student=$this->_dbAdapter->fetchAssoc ( $sql );

		    //查询每个学校的老师数 
		    $sql="select schoolid,count(distinct teacherid) as num from teacher_class_duty_v where inactive=0 ".$where." group by schoolid";
			//echo $sql;echo '<br />';
			$teacherlist=$this->_dbAdapter->fetchPairs ( $sql ); 
		    foreach($teacherlist as $key => $value){
				if(isset($student[$key]) && !isset($student[$key]['teacher'])){
					$student[$key]['teacher']=0;
				}
				$student[$key]['teacher']=$value;
			}	
			
			//获取天传入的变量
			$start_date = $this->_request->getParam ( 'start_date' ); 
			$end_date =  $this->_request->getParam ( 'end_date' );
			
			$now_date=date('Y-m-d');
			if(strtotime($start_date)<=0 || strtotime($start_date) > strtotime($now_date))
				$start_date=$now_date;
				
			if(!strtotime($end_date) || $end_date>$now_date)
				$end_date=$now_date;
			
			if($start_date>$end_date)
				$end_date=$start_date;

			$this->view->start_date=$start_date;
			$this->view->end_date=$end_date;

		    //在线班级 
			if(!empty($where)){
				$where_m=' and b.groupid='.$groupid;
			}else{
				$where_m='';
			}  
		    $where_class=' and b.day>='.date('ymd',strtotime($start_date)).' and b.day<='.date('ymd',strtotime($end_date)); 	 
		    $sql='select  b.schoolid,count(distinct b.teacherid) as num  from teacher_class a LEFT JOIN online_class b on a.teacherid=b.teacherid  where b.schoolid in('.implode(",",array_keys($student)).') '.$where_class.$where_m.' and b.teacherid<>0 and a.inactive=0 group by  b.schoolid ';	
				
		    $online_class=$this->_dbAdapter->fetchPairs ( $sql ); 
			
		    foreach($online_class as $key => $value){
				if(isset($student[$key]) && !isset($student[$key]['online_class'])){
				    $student[$key]['online_class']=0;
				}
				$student[$key]['online_class']=$value;
		    } 
			
		    //家长上线数
	        $where_student=' and day>='.date('ymd',strtotime($start_date)).' and day<='.date('ymd',strtotime($end_date)); 	
	        $sql='select schoolid,count(distinct pid) as num from  online_student where schoolid in('.implode(",",array_keys($student)).') '.$where_student.$where.' and pid<>0  group by  schoolid '; 
			// $sql;exit;
     		$online_student=$this->_dbAdapter->fetchPairs ( $sql ); 
	        foreach($online_student as $key =>$value){
				if(isset($student[$key]) && !isset($student[$key]['online_student'])){
				    $student[$key]['online_student']=0;
				}
				$student[$key]['online_student']=$value;
		    }
		
			foreach($student as $key=>$value){
				if(isset($student[$key]) && !isset($student[$key]['online_student_lv'])){
				    $student[$key]['online_student_lv']=0;
				}
				$student[$key]['online_student_lv']=round($value['online_student']/$value['student'],2)*100;
				$student[$key]['online_student_lv'].='%';
				
				if(isset($student[$key]) && !isset($student[$key]['online_class_lv'])){
				    $student[$key]['online_class_lv']=0;
				}
				$student[$key]['online_class_lv']=round($value['online_class']/$value['teacher'],2)*100;
				$student[$key]['online_class_lv'].='%';
				
			}
			$this->view->student=$student;

		} catch ( Exception $e ) {
			$this->feedback ( $e->getMessage (), 'note', 'javascript:window.history.back();', 'warning' );
		}
	}


//查看班级统计
	public function statisclassAction(){
		try {
			$this->view->groups=$groupid = ( int ) $this->_request->getParam ( 'groupid' );
			$this->view->schoolid=$schoolid = ( int ) $this->_request->getParam ( 'schoolid' );
			if(!$groupid){
				//跳转到机构列表
				throw new Exception ( '参数错误' );
			}

			//获取该校区班级
			$sql="select uid,classname from class where groupid=".$groupid." and isdel=0 and schoolid=".$schoolid." order by uid asc";
			$class=$this->_dbAdapter->fetchPairs($sql);
			//print_r($class);
		    //获取每个校区学生数
		    $sql="select classid,count(distinct studentid) as num from student_class_v where inactive=0 and schoolid=".$schoolid." group by classid order by classid asc";
		    $student=$this->_dbAdapter->fetchPairs($sql);
			//print_r($student);
			//获取天传入的变量
			$start_date = $this->_request->getParam ( 'start_date' ); 
			$end_date =  $this->_request->getParam ( 'end_date' );
			
			$now_date=date('Y-m-d');
			if(strtotime($start_date)<=0 || strtotime($start_date) > strtotime($now_date))
				$start_date=$now_date;
				
			if(!strtotime($end_date) || $end_date>$now_date)
				$end_date=$now_date;
			
			if($start_date>$end_date)
				$end_date=$start_date;

			$this->view->start_date=$start_date;
			$this->view->end_date=$end_date;

			//获取每个校区发布的图片数
		    $sql='SELECT uid_class,COUNT(articlefilesid) as num FROM `article_files_class_v` where groupid='.$groupid.' and  isdelete=0 and ischecked=1 and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'" and schoolid='.$schoolid.' GROUP BY uid_class';
			$picture=$this->_dbAdapter->fetchPairs($sql);
			//print_r($picture);exit;
			$this->view->picture=$picture;
			$this->view->class=$class;
			$this->view->student=$student;
			
		} catch ( Exception $e ) {
			$this->feedback ( $e->getMessage (), 'note', 'javascript:window.history.back();', 'warning' );
		}
	}

//生成曲线图

public function lineAction() {
		try {
			$this->view->groups=$groupid = ( int ) $this->_request->getParam ( 'groups' );
		     $school_id = $this->_request->getParam ( 'school' ); 		
		     $this->view->schoolid=$school_id;  
		  
			 if($school_id){ 
				 $schoolarray=explode('_',$school_id);  
				 $schoolid=$schoolarray['1'];
			 }				 
			 if($schoolid){
				 $this->view->schoolid= $school_id; 
			 } 
			//获取传参
			$start_date = $this->_request->getParam ( 'start_date' ); 
			$end_date =  $this->_request->getParam ( 'end_date' );

			//时间段判断
			$now_date=date('Y-m-d');
			if(strtotime($start_date)<=0 || strtotime($start_date) > strtotime($now_date))
				$start_date=$now_date;
			if(!strtotime($end_date) || $end_date>$now_date)
				$end_date=$now_date;
			if($start_date>$end_date)
				$end_date=$start_date;
			$this->view->start_date=$start_date;
			$this->view->end_date=$end_date;
			//print_r($schoolid);exit;
			
			if($schoolid){
				//获取本校区学生数
				$sql='select count(a.uid) as num from student a LEFT JOIN school b on a.schoolid=b.schoolid where b.schoolid='.$schoolid;
				$studentnum=$this->_dbAdapter->fetchOne($sql);
				//print_r($studentnum);exit;
				//获取周起始时间
				$weektimearray=$this->get_time_arr($start_date, $end_date, 'week');
				//print_r($arr);exit;
	/*Array ( [0] => Array ( [0] => 1393084800 [1] => 1393689599 ) [1] => Array ( [0] => 1393689600 [1] => 1394294399 ) [2] => Array ( [0] => 1394294400 [1] => 1394899199 ) [3] => Array ( [0] => 1394899200 [1] => 1395071999 ) ) */	
			
				$online_totalnum=array();
				$parentsonline=array();
				$huoyuedu=array();
				$fugailv=array();
				$picnum=array();
				foreach($weektimearray as $key=>$value){
					$start=date('ymd',$value[0]);
					$end=date('ymd',$value[1]);
					//登录总次数
					$sql="select sum(usednum) as num from online_student where day>='".$start."' and day<='".$end."' and groupid=".$groupid." and schoolid=".$schoolid;
					$online_totalnum=$this->_dbAdapter->fetchOne($sql);
					
					//上线家长人数
					$sql='select COUNT(DISTINCT(uid)) as num from online_student where day>="'.$start.'" and day<="'.$end.'" and pid<>0 and schoolid='.$schoolid;
					$parentsonline = $this->_dbAdapter->fetchOne ( $sql ); 
					
					//活跃度
					$huoyuedu[$start]=round($online_totalnum/$parentsonline,0);
					
					//覆盖率
					$fugai[$start]=round($parentsonline/$studentnum*100,0);
					if($fugai[$start])
						$fugailv[$start]=$fugai[$start] .'%';
					
					//人均照片和视频数 a.fext<>'mp4'
					$updatetime_start=date('Y-m-d H:i:s',$value[0]);
					$updatetime_end=date('Y-m-d H:i:s',$value[1]);
					$sql="select count(case when a.ischecked=1 and a.isdelete=0 and a.articlekey<>'' then 'picnum' end) as picnum from article_files_class_v a LEFT JOIN school b on a.schoolid=b.schoolid where b.schoolid=".$schoolid." and a.updatetime>='".$updatetime_start."' and a.updatetime<='".$updatetime_end."' ";
					$picture = $this->_dbAdapter->fetchOne ( $sql );
					$picnum[$start]=round($picture/$studentnum,2);
				}				
			
				$this->view->huoyuedu=$huoyuedu;
				$this->view->fugailv=$fugailv;
				$this->view->picnum=$picnum;
	//生成flash的start --------------	
	   
	   //设置时间轴 
		//查询本班级的学生总数　　
		
				$xmlData="<chart palette='2' caption='".$start_date.'~'.$end_date."  云中校车使用情况统计图表' rotateNames='0' showValues='0' divLineDecimalPrecision='1' limitsDecimalPrecision='1' PYAxisName='云中校车使用情况' SYAxisName='数量'  formatNumberScale='0' >";	 
				$categories="<categories>";
				$starttime=strtotime($start_date);
				$endtime =strtotime($end_date); 
				$timearray=array();
				foreach($weektimearray as $key=>$value){
					$start=date('ymd',$value[0]);
					$end=date('ymd',$value[1]);
					$timearray[$start]=$start.'-'.$end;
				}
				 
				foreach($timearray as $key=>$value){
					$categories.="<category label='".$value."' />";
				}		 
				$categories.="</categories>";  
	
				$xmlData=$xmlData.$categories; 
				$this->view->timearray=$timearray;
	
	//照片总数			
				$xmlData.="<dataset  parentYAxis='S' seriesName='人均照片和视频数' seriesName='比例' >";  
				foreach($timearray as $key=>$value){
					if($picnum[$key])
						$xmlData.="<set value='".$picnum[$key]."' />";
					else
						$xmlData.="<set value='0' />";
				}
				$xmlData.="</dataset>"; 
	
	
	//活跃度
				$xmlData.="<dataset  parentYAxis='S' seriesName='活跃度' seriesName='比例' >";  
				foreach($timearray as $key=>$value){
					if($huoyuedu[$key])
						$xmlData.="<set value='".$huoyuedu[$key]."' />";
					else
						$xmlData.="<set value='0' />";
				}
				$xmlData.="</dataset>"; 
	
	//覆盖率			
				$xmlData.="<dataset  parentYAxis='S' seriesName='覆盖率' seriesName='比例' >";  
				foreach($timearray as $key=>$value){
					if($fugai[$key])
						$xmlData.="<set value='".$fugai[$key]."' />";
					else
						$xmlData.="<set value='0' />";
				}
				$xmlData.="</dataset>"; 
				
	
				$xmlData.="</chart>";  
				$this->view->xmlData=$xmlData;
		}	
					
		} catch ( Exception $e ) {
			$this->feedback ( $e->getMessage (), 'note', 'javascript:window.history.back();', 'warning' );
		}
	} 
	private	function get_time_arr($s,$e,$m_or_w)
	{
		$arr=array();
		$start=strtotime($s." 00:00:00");
		$end=strtotime($e." 23:59:59");
		if($m_or_w=='week')
		{
			$s_w=date('w',$start);
			$f_w=7-$s_w;
		}else 
		{
			$allday=date('t',$start);
			$today=date('d',$start);
			$f_w=$allday-$today+1;
		}
		if($f_w){
				$f_end=$start+86400*$f_w-1;
			}else{
				$f_end=$start+86400-1;
			}
			$new_end=$f_end;
			if($end<$new_end){
				$arr[]=array($start,$end);
				return $arr;
			}
		while ($end>$new_end){
				$arr[]=array($start,$new_end);
				$start=$new_end+1;
				if($m_or_w=='week'){
					$day=7;
				}else {
					$day=date('t',$new_end+10);
				}
				$new_end=$new_end+$day*86400;
			}
			if($m_or_w=='week'){
				$fullday=7;
			}else{
			$fullday=date('t',$new_end);}
			$arr[]=array($new_end-$fullday*86400+1,$end);
			return $arr;
	}

//查看已发布
	public function articlelistAction(){
		try {
			   $groupid = ( int ) $this->_request->getParam ( 'groups' );
			   $school_id = $this->_request->getParam ( 'school' ); 		
			   $classid  = $this->_request->getParam ( 'class' ); 
				$curpage = ( int ) $this->_request->getParam ( 'page' );
				//	print_r($curpage);exit;
			   $this->view->groups=$groupid; 
			   $this->view->school_id=$school_id;  
			   $this->view->class=$classid; 
			   
			   $this->view->uid_class = $classid;
			   $uid_class=$classid;
			   if($school_id){ 
				  $schoolarray=explode('_',$school_id);  
				  $schoolid=$schoolarray['1'];
			   }
			   $where='';  
			   if($groupid){
				   $where .=' and  groupid="'.$groupid.'" ';
				   $this->view->groupid= $groupid; 
			   }
					 
			   if($schoolid){
					$where .=' and  schoolid="'.$schoolid.'" '; 
					$this->view->schoolid= $schoolid; 
			   } 
					
			   if($classid){
					$where .=' and  classid="'.$classid.'" ';
					$this->view->classid= $classid; 
			   }
			   
			   if($groupid && $schoolid && $classid){
					//取学校名
					$sql='select schoolname from school where groupid='.$groupid.' and schoolid='.$schoolid;
					$this->view->schoolname=$this->_dbAdapter->fetchOne ( $sql );
					//显示班级列表
					$sql="SELECT classname FROM class where isdel=0 and groupid=".$groupid." and  schoolid=".$schoolid." and classid=".$classid." order by uid asc ";
					//echo $sql;exit;
					$classname=$this->_dbAdapter->fetchOne ( $sql );
					$this->view->classname=$classname;
			
				//获取已发日志列表
				if($uid_class){
					$sql='select tagid,tagname from school_tag where schoolid='.$schoolid.' and isdelete=0 ';
					$taglist=$this->_dbAdapter->fetchPairs($sql);
					//取班级学生备选表
					$sql='select studentid,cnname from group_student_class_parents_v where ismainid=1 and groupid='.$groupid.' and uid='.$uid_class;
					$this->view->student=$this->_dbAdapter->fetchPairs ( $sql );
					
					//print_r($student);exit;
					//选取班级已发布日志
					$count = 20;
					//$curpage = ( int ) $this->_request->getParam ( 'page' );
					//print_r($curpage);exit;
					
					if (! $curpage)
							$curpage = 1;
						$offset = ($curpage - 1) * $count; 
				
					
					$sql="select SQL_CALC_FOUND_ROWS a.articlekey,a.articleid,a.content,a.title,a.createteacherid,a.classid,a.photosetid,a.addtime,a.tag,b.teachername from article a left join teacher_class_duty_v b on a.classid=b.classid and a.createteacherid=b.teacherid  where a.groupid=".$groupid." and a.classid=".$uid_class." and a.isdelete=0 and a.articlekey<>''  ORDER BY a.addtime desc limit $offset,$count ";
					//echo $sql;exit;
					$articlelist=$this->_dbAdapter->fetchAssoc($sql); 
					
					
					$param ['file'] = '/' . $this->controller . '/' . $this->action;
					$param ['totalnum'] = $this->_dbAdapter->fetchOne ( 'SELECT FOUND_ROWS()' ); 
					$param ['perpagenum'] = $count; // 每页显示的数目
					$param ['disnum'] = 11; // 取单数显示，当前页停在中间
					$param ['curpage'] = $curpage; // 当前页码
					$this->view->allnum=$param ['totalnum'];
					$this->view->pagenum=$param ['curpage'];
					
					$page = new MyClass_Page ( $param );
					$page->setvar ( array ('groups' => $groupid,'school' => $groupid.'_'.$schoolid,'class' => $uid_class) );
					$this->view->page= $page->getNumPage (); 
					
					//print_r($articlelist);exit;
					foreach($articlelist as $key=>$value){
						$tag=array();
						$tag=explode(',',$value['tag']);
						foreach($tag as $key1=>$value1){
							if($taglist[$value1]){
								$articlelist[$key]['tag_desc'][]=$taglist[$value1];
							}else{
								$articlelist[$key]['tag_desc'][]=$value1;
							}
						}
						
						$articlelist[$key]['title']=str_replace("\n",'<br />',$value['title']);
						$articlelist[$key]['title']=stripslashes($value['title']);
						$articlelist[$key]['content']=str_replace("\n",'<br />',$value['content']);
						$articlelist[$key]['content']=stripslashes($value['content']);
					}
					if($articlelist){
						//获取图片
						$sql="select articlefilesid,articlekey,concat('http://',cloudhost,cloudpath,cloudfilename,'.',fext) as avatar,fext,intro from article_files where groupid=".$groupid." and isdelete=0 and articlekey in ('".implode("','",array_keys($articlelist) )."') ";
						$frow_tmp=$this->_dbAdapter->fetchAll ($sql);
						//print_r($frow_tmp);exit;
						
						foreach($frow_tmp as $k => $v){
							$articlelist[$v['articlekey']]['filecount'][]=$v;
						}
						unset($frow_tmp);
						
						//获取接收人studentid
						$sql='select distinct(studentid) from article_student where articlekey in ("'.implode('","',array_keys($articlelist) ).'") ';
						$reciever_tmp=$this->_dbAdapter->fetchCol ($sql);	
						
						//获取接收人姓名
						$sql='select studentid,cnname from student where studentid in ("'.implode('","',array_values($reciever_tmp) ).'")';					
						$reciever_name=$this->_dbAdapter->fetchPairs ($sql);
	
						$recieverlist=array();
						foreach ($articlelist as $k=>$v)
						{
							$recieverlist[]=$v['articlekey'];
						}
						if($recieverlist){
							$sql1='select * from article_student where articlekey in ("'.implode('","',$recieverlist ).'")'; 
							$frow=$this->_dbAdapter->fetchAll ($sql1);
							foreach($frow as $k => $v){
								$articlelist[$v['articlekey']]['people'][$v['studentid']]=$reciever_name[$v['studentid']];
								$articlelist[$v['articlekey']]['name']=implode(',',array_values($articlelist[$v['articlekey']]['people']));								
							}
						}
						unset($reciever_tmp);
						unset($reciever_name);
					}
					
					foreach($articlelist as $key=>$value){
						$articlelist[$key]['addtime']=date('Y-m-d H:i',$value['addtime']);
						}
						//print_r($articlelist);exit;
						$this->view->articlelist=$articlelist;
				}
			   }
			
		} catch ( Exception $e ) {
			$this->feedback ( $e->getMessage (), 'note', 'javascript:window.history.back();', 'warning' );
		}
	}






   public function groupsAction() {
		try { 
		 $p =$this->_request->getPost ();
         $sql="select * from  groups ";
	     $result = $this->_dbAdapter->fetchAll ($sql);
		 
		 $str.='<option value="0" >==请选择机构==</option>';
		 for($i = 0; $i<count($result); $i++)
		{
			$str .= "<option";
			if($p['jigouid']==$result[$i]['groupid']){
				$str .=" selected=selected ";
			}
	      $str .= " value='".$result[$i]['groupid']."'>".$result[$i]['groupname']."</option>";
		}
		  
	    echo $str;  exit;
	} catch ( Exception $e ) {
			$this->feedback ( $e->getMessage (), 'note', 'javascript:window.history.back();', 'warning' );
		}
	}
	
	//获取所有的校园列表
	  public function schoollistAction() {
		try { 
           if (! $this->isPost ()) {
					throw new Exception ( '参数错误！' );
			}			
		  $p =$this->_request->getPost (); 
		   
		  $array=explode('_',$p['sid']);
		  
		  $sql="SELECT * FROM `groups` where groupid='".$p['id']."' ";
		  $result = $this->_dbAdapter->fetchRow ($sql);
		    $sql="select * from  school where groupid='".$p['id']."' ";   
			$schoollist=$this->_dbAdapter->fetchAll ($sql);
	                 
            $str.='<option value="0" >==请选择学校==</option>';
			// echo $p['id'];   exit;
		 for($i = 0; $i<count($schoollist); $i++)
		{
		  $str .= "<option ";
		  
		  if ($array['1']==$schoollist[$i]['schoolid']){
		      $str .=" selected=selected";
		  }
		   $str .= " value='".$p['id'].'_'.$schoollist[$i]['schoolid']."'>";
		   $str .= $schoollist[$i]['schoolname']."</option>";
		   
		}
		   
	      echo $str;  exit;
	} catch ( Exception $e ) {
			$this->feedback ( $e->getMessage (), 'note', 'javascript:window.history.back();', 'warning' );
		}
	}
	
	
	
	//获取所有的班级信息
	  public function classlistAction() {
		try { 
           if (! $this->isPost ()) {
					throw new Exception ( '参数错误！' );
			}			
		    $p =$this->_request->getPost ();  
		    $array=explode('_',$p['id']); 
			 
		    $sql="SELECT * FROM `groups` where groupid='".$array['0']."' ";
		    $result = $this->_dbAdapter->fetchRow ($sql);
	      
		  $sql="select * from  class  where isdel=0 and schoolid='".$array[1]."' ";   
		  $classlist= $this->_dbAdapter->fetchAll ($sql);
	      $str.='<option value="0" >==请选择班级==</option>';
		  	 
		 for($i = 0; $i<count($classlist); $i++)
		{
		  $str .= "<option ";
		  if($p['cid']==$classlist[$i]['classid']){
		     $str .=" selected=selected";
		  } 
		  $str .= "  value='".$classlist[$i]['classid']."'>".$classlist[$i]['classname']."</option>";
		    
		} 
		   
	      echo $str;  exit;
	} catch ( Exception $e ) {
			$this->feedback ( $e->getMessage (), 'note', 'javascript:window.history.back();', 'warning' );
		}
	}



public function exportexcelAction() {
		try {  

            $start_date = $this->_request->getParam ( 'start_date' ); 
	    	$end_date =  $this->_request->getParam ( 'end_date' );
			$name=$start_date.'至'.$end_date;
			$now_date=date('Y-m-d');
			if(strtotime($start_date)<=0 || strtotime($start_date) > strtotime($now_date))
				$start_date=$now_date;
				
			if(!strtotime($end_date) || $end_date>$now_date)
				$end_date=$now_date;

			
			if($start_date>$end_date)
				$end_date=$start_date;  
			
			 
			$groupid = ( int ) $this->_request->getParam ( 'groupid' );
			if(!$groupid){
				//跳转到机构列表
				throw new Exception ( '参数错误' );
			}
			 
			$objExcel = new PHPExcel();
			
			$objExcel->getProperties()->setCreator("Student Report");
			$objExcel->getProperties()->setLastModifiedBy("mac_top");
			$objExcel->getProperties()->setTitle($name."详细统计 Student Report");
			$objExcel->getProperties()->setSubject($name."详细统计 Student Report");
			$objExcel->getProperties()->setDescription($name."详细统计 Student Report");
			$objExcel->getProperties()->setKeywords($name."详细统计 Student Report");
			$objExcel->getProperties()->setCategory($name."详细统计 Student Report"); 
            
			
			$i=0;
			$k0="编号";
			$k1="学校名称";
			$k2="学生数";
			$k3="周人均照片";
			$k4="累计人均照片";
			$k5="周人均视频";
			$k6="累积人均视频";
			$k7="本周付费家长数";
			$k8="未付费家长数";
			$k9="付费率";
			$k10=$name."统计信息"; 
			
		    $objExcel->getActiveSheet()->setTitle($k10);  
		    $objExcel->setActiveSheetIndex(0);	
 		    $objExcel->getActiveSheet()->setCellValue('a1', $k0);
			$objExcel->getActiveSheet()->setCellValue('b1', $k1);
			$objExcel->getActiveSheet()->setCellValue('c1', $k2);
			$objExcel->getActiveSheet()->setCellValue('d1', $k3);
			$objExcel->getActiveSheet()->setCellValue('e1', $k4);
			$objExcel->getActiveSheet()->setCellValue('f1', $k5);
			$objExcel->getActiveSheet()->setCellValue('g1', $k6);
			$objExcel->getActiveSheet()->setCellValue('h1', $k7);
			$objExcel->getActiveSheet()->setCellValue('i1', $k8);
			$objExcel->getActiveSheet()->setCellValue('j1', $k9);
			
			
			$objExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(20);//表示设置A这一列的宽度,以下一样   
			$objExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);//表示设置A这一列的宽度,以下一样   
			$objExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);//表示设置A这一列的宽度,以下一样   
			$objExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);//表示设置A这一列的宽度,以下一样   
			$objExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);//表示设置A这一列的宽度,以下一样   
			$objExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(20);//表示设置A这一列的宽度,以下一样   
			$objExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);//表示设置A这一列的宽度,以下一样   
			$objExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(20);//表示设置A这一列的宽度,以下一样   
			$objExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setWidth(20);//表示设置A这一列的宽度,以下一样   
			$objExcel->setActiveSheetIndex(0)->getColumnDimension('J')->setWidth(20);//表示设置A这一列的宽度,以下一样   
			
			$objExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        	$objExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setWrapText(TRUE); 
			$objExcel->setActiveSheetIndex(0)->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        	$objExcel->setActiveSheetIndex(0)->getStyle('A2')->getAlignment()->setWrapText(TRUE); 
			$objExcel->setActiveSheetIndex(0)->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        	$objExcel->setActiveSheetIndex(0)->getStyle('A3')->getAlignment()->setWrapText(TRUE); 
			$objExcel->setActiveSheetIndex(0)->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('A4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        	$objExcel->setActiveSheetIndex(0)->getStyle('A4')->getAlignment()->setWrapText(TRUE); 
			$objExcel->setActiveSheetIndex(0)->getStyle('A5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('A5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        	$objExcel->setActiveSheetIndex(0)->getStyle('A5')->getAlignment()->setWrapText(TRUE); 
			$objExcel->setActiveSheetIndex(0)->getStyle('A6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('A6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        	$objExcel->setActiveSheetIndex(0)->getStyle('A6')->getAlignment()->setWrapText(TRUE); 
			$objExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        	$objExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()->setWrapText(TRUE);  
			$objExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()->setWrapText(TRUE); 
			$objExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()->setWrapText(TRUE); 
			$objExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        	$objExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()->setWrapText(TRUE);  
			$objExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        	$objExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()->setWrapText(TRUE);  
			$objExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()->setWrapText(TRUE); 
			$objExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('h1')->getAlignment()->setWrapText(TRUE);
			 
			$objExcel->setActiveSheetIndex(0)->getStyle('I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('I1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('i1')->getAlignment()->setWrapText(TRUE); 
			$objExcel->setActiveSheetIndex(0)->getStyle('J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('J1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        	$objExcel->setActiveSheetIndex(0)->getStyle('j1')->getAlignment()->setWrapText(TRUE); 
			
         
			//显示校区列表
			$sql="SELECT schoolid,schoolname FROM school where groupid=".$groupid." order by schoolid asc";
			$school=$this->_dbAdapter->fetchAssoc ( $sql );
			$this->view->school= $school; 
			//print_r($school);
			
		    //获取每个校区学生数
		    $sql="select schoolid,count(studentid) as num from student where groupid=".$groupid." group by schoolid";
		    $student=$this->_dbAdapter->fetchPairs($sql);
		    //print_r($student);
			

			//获取期间每个校区发布的图片数
		    $sql='SELECT schoolid,COUNT(articlefilesid) as num FROM `article_files_class_v` where groupid='.$groupid.' and  fext<>"mp4" and isdelete=0 and ischecked=1 and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'" GROUP BY schoolid';
			$picture=$this->_dbAdapter->fetchPairs($sql);
			
			//获取期间每个校区发布的视频数
		    $sql='SELECT schoolid,COUNT(articlefilesid) as num FROM `article_files_class_v` where groupid='.$groupid.' and fext="mp4" and isdelete=0 and ischecked=1 and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'" GROUP BY schoolid';
			$video=$this->_dbAdapter->fetchPairs($sql);
			
			//获取期间付费学生家长数
			$sql='SELECT b.schoolid,COUNT(a.uid_student) as num from pay_order a LEFT JOIN class_student_v b on a.uid_student=b.uid_student WHERE groupid='.$groupid.' and isdelete=0 and isover=0 and from_unixtime(addtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(addtime,"%Y-%m-%d")<="'.$end_date.'" GROUP BY schoolid;';
			$pay=$this->_dbAdapter->fetchPairs($sql);
			
			//获取所有付费学生家长数
			$sql='SELECT b.schoolid,COUNT(a.uid_student) as num from pay_order a LEFT JOIN class_student_v b on a.uid_student=b.uid_student WHERE groupid='.$groupid.' and isdelete=0 and isover=0  GROUP BY schoolid;';
			$payall=$this->_dbAdapter->fetchPairs($sql);
			
			//获取每个校区学生接收的图片、视频数
			foreach($school as $key=>$value){
				//学生数
				if($student[$key]){
					$school[$key]['student']=$student[$key];
				}else{
					$school[$key]['student']=0;
				}
				
				//付费数
				if($pay[$key]){
					$school[$key]['pay']=$pay[$key];
				}else{
					$school[$key]['pay']=0;
				}
				if($payall[$key]){
					$school[$key]['payed']=$payall[$key];
					$school[$key]['unpay']=$student[$key]-$payall[$key];
					$school[$key]['payrate']=round($payall[$key]/$student[$key]*100,2);
					$school[$key]['payrate'].='%';
				}else{
					$school[$key]['payed']=0;
					$school[$key]['unpay']=$student[$key];
					$school[$key]['payrate']=0;
				}
				
				//期间老师拍照数
				if($picture[$key]){
					$school[$key]['picture']=$picture[$key];
					$school[$key]['sendpicrate']=round($picture[$key]/$student[$key],0);
				}else{
					$school[$key]['picture']=0;
					$school[$key]['sendpicrate']=0;
				}
				//期间学生接收照片数
				$sql='SELECT articlefilesid,memberlist FROM `article_files_class_v` where groupid='.$groupid.' and fext<>"mp4" and isdelete=0 and ischecked=1 and schoolid='.$key.' and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'"';
				$recieve=$this->_dbAdapter->fetchPairs($sql);
				//print_r($recieve);
				$rec=0;
				if($recieve){
					foreach($recieve as $key1=>$value1){
						$rec+=count(explode(',',$value1));
					}
				}
				$school[$key]['recpicnum']=$rec;
				$school[$key]['recpicrate']=round($rec/$student[$key],0);
				
				//所有时间学生接收照片数
				$sql='SELECT articlefilesid,memberlist FROM `article_files_class_v` where groupid='.$groupid.' and fext<>"mp4" and isdelete=0 and ischecked=1 and schoolid='.$key;
				$recieve1=$this->_dbAdapter->fetchPairs($sql);
				$rec=0;
				if($recieve1){
					foreach($recieve1 as $key2=>$value2){
						$rec+=count(explode(',',$value2));
					}
				}
				$school[$key]['recpicallnum']=$rec;
				$school[$key]['recpicallrate']=round($rec/$student[$key],0);
				
				//期间老师拍视频数
				if($video[$key]){
					$school[$key]['video']=$video[$key];
					$school[$key]['sendvideorate']=round($video[$key]/$student[$key],0);
				}else{
					$school[$key]['video']=0;
					$school[$key]['sendvideorate']=0;
				}
				//期间学生接收视频数
				$sql='SELECT articlefilesid,memberlist FROM `article_files_class_v` where groupid='.$groupid.' and fext="mp4" and isdelete=0 and ischecked=1 and schoolid='.$key.' and from_unixtime(uploadtime,"%Y-%m-%d")>="'.$start_date.'" and from_unixtime(uploadtime,"%Y-%m-%d")<="'.$end_date.'"';
				$recievevideo=$this->_dbAdapter->fetchPairs($sql);
				$rec=0;
				if($recievevideo){
					foreach($recievevideo as $key3=>$value3){
						$rec+=count(explode(',',$value3));
					}
				}
				$school[$key]['recvideonum']=$rec;
				$school[$key]['recvideorate']=round($rec/$student[$key],0);
				
				//所有时间学生接收视频数
				$sql='SELECT articlefilesid,memberlist FROM `article_files_class_v` where groupid='.$groupid.' and fext="mp4" and isdelete=0 and ischecked=1 and schoolid='.$key;
				$recieve2=$this->_dbAdapter->fetchPairs($sql);
				$rec=0;
				if($recieve2){
					foreach($recieve2 as $key4=>$value4){
						$rec+=count(explode(',',$value4));
					}
				}
				$school[$key]['recvideoallnum']=$rec;
				$school[$key]['recvideoallrate']=round($rec/$student[$key],0);
				
				//
			}//exit;
			//print_r($school);exit;
			$schoolall=array();
			$schoolall=array_values($school);		 
			//print_r(array_values($school));exit;
		//  print_r($school); 
		//  exit;
		   if($school){
			  
			 for($i=0;$i<count($schoolall);$i++){
			  	             
			    $objExcel->getActiveSheet()->setCellValue('a'.($i+2),$i+1);	 
			 	$objExcel->getActiveSheet()->setCellValue('b'.($i+2),$schoolall[$i]['schoolname']); 
		        $objExcel->getActiveSheet()->setCellValue('c'.($i+2), $schoolall[$i]['student']);	 
				$objExcel->getActiveSheet()->setCellValue('d'.($i+2),$schoolall[$i]['recpicrate']);
				$objExcel->getActiveSheet()->setCellValue('e'.($i+2),$schoolall[$i]['recpicallrate']); 
			    $objExcel->getActiveSheet()->setCellValue('f'.($i+2),$schoolall[$i]['recvideorate']); 
				$objExcel->getActiveSheet()->setCellValue('g'.($i+2),$schoolall[$i]['recvideoallrate']);
			    $objExcel->getActiveSheet()->setCellValue('h'.($i+2),$schoolall[$i]['pay']);  
			    $objExcel->getActiveSheet()->setCellValue('i'.($i+2),$schoolall[$i]['unpay']);  
			 	$objExcel->getActiveSheet()->setCellValue('j'.($i+2),$schoolall[$i]['payrate']);   
			    
			    }
			     
			   			  
		   }else{   
			 $this->feedback ( 'nodata', 'note', '/'.$this->controller.'/export', 'tip', 3 );  
		   }
			 
		    
			$ex='2005';
			$timestamp = time();
			//echo chr((64+2));exit;
			if($ex == '2007') { //导出excel2007文档
				header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
				header('Content-Disposition: attachment;filename="links_out'.$timestamp.'.xlsx"');
				header('Cache-Control: max-age=0');
				$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
				$objWriter->save('php://output');
				exit;
			} else {  //导出excel2003文档
				$str = iconv("utf-8", "gbk","详细统计 Student Report");
				header("Content-Disposition:attachment;filename=".$str.".xls"); 
				header("Content-Type:application/octet-stream");  
				header("Content-Transfer-Encoding:binary");  
				header("Pragma:no-cache");  
				header("Pragma: public");           			
				//header('Cache-Control: max-age=0');
				$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
				$objWriter->save('php://output');
				exit;
			}
				 
		  exit;
         $this->_smarty->display ( $this->controller . '/' . $this->action . '.tpl' );
		} catch ( Exception $e ) {
			$this->feedback ( $e->getMessage (), 'note', 'javascript:window.history.back();', 'warning' );
		}
	}

	 
	 //以下内容保持不变
	public function noRouteAction() {
		$this->_redirect ( '/' );
	}
	
	public function __call($method, $args) {
		if ('Action' == substr ( $method, - 6 )) {
			$url = '/' . $this->controller . '/index';
			return $this->_redirect ( $url );
		}
		throw new Exception ( 'Invalid method' );
	}
}

